
<!DOCTYPE html>

<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>第一章 Excel与数据格式 &#8212; free-excel</title>
<script>
  document.documentElement.dataset.mode = localStorage.getItem("mode") || "";
  document.documentElement.dataset.theme = localStorage.getItem("theme") || "light"
</script>

  <!-- Loaded before other Sphinx assets -->
  <link href="../_static/styles/theme.css?digest=92025949c220c2e29695" rel="stylesheet">
<link href="../_static/styles/pydata-sphinx-theme.css?digest=92025949c220c2e29695" rel="stylesheet">


  <link rel="stylesheet"
    href="../_static/vendor/fontawesome/5.13.0/css/all.min.css">
  <link rel="preload" as="font" type="font/woff2" crossorigin
    href="../_static/vendor/fontawesome/5.13.0/webfonts/fa-solid-900.woff2">
  <link rel="preload" as="font" type="font/woff2" crossorigin
    href="../_static/vendor/fontawesome/5.13.0/webfonts/fa-brands-400.woff2">

    <link rel="stylesheet" type="text/css" href="../_static/pygments.css" />
    <link rel="stylesheet" type="text/css" href="../_static/css/s4defs-roles.css" />

  <!-- Pre-loaded scripts that we'll load fully later -->
  <link rel="preload" as="script" href="../_static/scripts/pydata-sphinx-theme.js?digest=92025949c220c2e29695">

    <script data-url_root="../" id="documentation_options" src="../_static/documentation_options.js"></script>
    <script src="../_static/jquery.js"></script>
    <script src="../_static/underscore.js"></script>
    <script src="../_static/_sphinx_javascript_frameworks_compat.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/sphinx_highlight.js"></script>
    <link rel="shortcut icon" href="../_static/logo.ico"/>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="第二章 Excel的快捷操作" href="chap2.html" />
    <link rel="prev" title="教程" href="index.html" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="docsearch:language" content="en">
  </head>
  
  
  <body data-spy="scroll" data-target="#bd-toc-nav" data-offset="180" data-default-mode="">
    <div class="bd-header-announcement container-fluid" id="banner">
      

    </div>

    
    <nav class="bd-header navbar navbar-light navbar-expand-lg bg-light fixed-top bd-navbar" id="navbar-main"><div class="bd-header__inner container-xl">

  <div id="navbar-start">
    
    
  


<a class="navbar-brand logo" href="../index.html">
  
  
  
  
    <img src="../_static/logo.svg" class="logo__image only-light" alt="Logo image">
    <img src="../_static/logo.svg" class="logo__image only-dark" alt="Logo image">
  
  
</a>
    
  </div>

  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbar-collapsible" aria-controls="navbar-collapsible" aria-expanded="false" aria-label="Toggle navigation">
    <span class="fas fa-bars"></span>
  </button>

  
  <div id="navbar-collapsible" class="col-lg-9 collapse navbar-collapse">
    <div id="navbar-center" class="mr-auto">
      
      <div class="navbar-center-item">
        <ul id="navbar-main-elements" class="navbar-nav">
    <li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../%E4%B8%BB%E9%A1%B5.html">
  主页
 </a>
</li>

<li class="toctree-l1 current active nav-item">
 <a class="reference internal nav-link" href="index.html">
  教程
 </a>
</li>

<li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../%E4%BD%9C%E8%80%85.html">
  作者
 </a>
</li>

<li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../Datawhale.html">
  Datawhale
 </a>
</li>

<li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../%E7%BB%83%E4%B9%A0%E9%A2%98%E5%8F%82%E8%80%83%E6%93%8D%E4%BD%9C.html">
  练习题参考操作
 </a>
</li>

    
</ul>
      </div>
      
    </div>

    <div id="navbar-end">
      
      <div class="navbar-end-item">
        <span id="theme-switch" class="btn btn-sm btn-outline-primary navbar-btn rounded-circle">
    <a class="theme-switch" data-mode="light"><i class="fas fa-sun"></i></a>
    <a class="theme-switch" data-mode="dark"><i class="far fa-moon"></i></a>
    <a class="theme-switch" data-mode="auto"><i class="fas fa-adjust"></i></a>
</span>
      </div>
      
      <div class="navbar-end-item">
        <ul id="navbar-icon-links" class="navbar-nav" aria-label="Icon Links">
        <li class="nav-item">
          <a class="nav-link" href="https://github.com/datawhalechina/free-excel" rel="noopener" target="_blank" title="GitHub"><span><i class="fab fa-github-square"></i></span>
            <label class="sr-only">GitHub</label></a>
        </li>
      </ul>
      </div>
      
    </div>
  </div>
</div>
    </nav>
    

    <div class="bd-container container-xl">
      <div class="bd-container__inner row">
          

<!-- Only show if we have sidebars configured, else just a small margin  -->
<div class="bd-sidebar-primary col-12 col-md-3 bd-sidebar">
  <div class="sidebar-start-items"><form class="bd-search d-flex align-items-center" action="../search.html" method="get">
  <i class="icon fas fa-search"></i>
  <input type="search" class="form-control" name="q" id="search-input" placeholder="在这里搜索" aria-label="在这里搜索" autocomplete="off" >
</form><nav class="bd-links" id="bd-docs-nav" aria-label="Main navigation">
  <div class="bd-toc-item active">
    <ul class="current nav bd-sidenav">
 <li class="toctree-l1 current active">
  <a class="current reference internal" href="#">
   第一章 Excel与数据格式
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap2.html">
   第二章 Excel的快捷操作
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap3.html">
   第三章 Excel的表合并
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap4.html">
   第四章 Excel函数-逻辑判断
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap5.html">
   第五章 Excel函数-文本函数
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap6.html">
   第六章 Excel函数-查找函数
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap7.html">
   第七章 Excel函数-动态函数
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap8.html">
   第八章Excel数据可视化
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap9.html">
   第九章Excel数据透视
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap10.html">
   第十章Excel看板
  </a>
 </li>
</ul>

  </div>
</nav>
  </div>
  <div class="sidebar-end-items">
  </div>
</div>


          


<div class="bd-sidebar-secondary d-none d-xl-block col-xl-2 bd-toc">
  
    
    <div class="toc-item">
      
<div class="tocsection onthispage mt-5 pt-1 pb-3">
    <i class="fas fa-list"></i> On this page
</div>

<nav id="bd-toc-nav">
    <ul class="visible nav section-nav flex-column">
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#id1">
   1.数据缘何而来
  </a>
  <ul class="nav section-nav flex-column">
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#id2">
     1.1 了解Excel
    </a>
   </li>
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#id3">
     1.2 Excel存储格式
    </a>
   </li>
  </ul>
 </li>
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#id4">
   2.Excel数据格式
  </a>
  <ul class="nav section-nav flex-column">
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#id5">
     2.1 单元格数据类型
    </a>
    <ul class="nav section-nav flex-column">
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id6">
       2.1.1 数字
      </a>
     </li>
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id7">
       2.1.2 文本
      </a>
     </li>
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id8">
       2.1.3 日期
      </a>
     </li>
    </ul>
   </li>
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#id9">
     2.2 单元格格式
    </a>
    <ul class="nav section-nav flex-column">
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id10">
       2.2.1 数值格式
      </a>
     </li>
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id11">
       2.2.2 文本格式
      </a>
     </li>
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id12">
       2.2.3 日期格式
      </a>
     </li>
    </ul>
   </li>
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#id13">
     2.3 转换单元格数据
    </a>
    <ul class="nav section-nav flex-column">
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id14">
       2.3.1 数值和文本相互转换
      </a>
     </li>
     <li class="toc-h4 nav-item toc-entry">
      <a class="reference internal nav-link" href="#id15">
       2.3.2 日期和文本相互转换
      </a>
     </li>
    </ul>
   </li>
  </ul>
 </li>
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#id16">
   练习
  </a>
 </li>
</ul>

</nav>
    </div>
    
    <div class="toc-item">
      
    </div>
    
  
</div>


          
          
          <div class="bd-content col-12 col-md-9 col-xl-7">
              
              <article class="bd-article" role="main">
                
  <div class="section" id="excel">
<h1>第一章 Excel与数据格式<a class="headerlink" href="#excel" title="Permalink to this heading">#</a></h1>
<div class="section" id="id1">
<h2>1.数据缘何而来<a class="headerlink" href="#id1" title="Permalink to this heading">#</a></h2>
<p>Execel是我们常见的用来进行数据处理的工具，其中Excel文件主要包含有2种格式分别是<code class="docutils literal notranslate"><span class="pre">xls</span></code>或<code class="docutils literal notranslate"><span class="pre">xlsx</span></code>，<code class="docutils literal notranslate"><span class="pre">xls</span></code>和<code class="docutils literal notranslate"><span class="pre">xlsx</span></code>主要区别如下：</p>
<div class="figure align-default">
<img alt="" src="../_images/1.0.png" />
</div>
<p>因此，推荐大家使用最新版本的Excel或者WPS进行相关的学习与操作。</p>
<div class="section" id="id2">
<h3>1.1 了解Excel<a class="headerlink" href="#id2" title="Permalink to this heading">#</a></h3>
<p>Excel文每个数据文件叫做工作簿，每个工作簿里面含有多个工作表，每个工作表里面的格子叫做单元格。</p>
<p>在Excel中点击新建文档后，Excel默认的文档名称是工作簿1，也就是一个Excel文档，就是一个工作簿</p>
<div class="figure align-default">
<img alt="" src="../_images/1.1.png" />
</div>
<p>在新建的工作簿中，可以看到左下方有<code class="docutils literal notranslate"><span class="pre">Sheet1</span></code>的标签，每个工作簿里面可以含有多个工作表，Excel表中最大支持255个工作表</p>
<div class="figure align-default">
<img alt="" src="../_images/1.2.png" />
</div>
<p>单元格就是工作表中间每一个格子</p>
<div class="figure align-default">
<img alt="" src="../_images/1.3.png" />
</div>
<p>在Excel最上方是工具栏，如果希望在Excel中导入第三方插件，可以点击<code class="docutils literal notranslate"><span class="pre">文件</span></code>–&gt;<code class="docutils literal notranslate"><span class="pre">选项</span></code>–&gt;<code class="docutils literal notranslate"><span class="pre">自定义功能区</span></code>点击右侧的√，即可导入相关模块</p>
<div class="figure align-default">
<img alt="" src="../_images/1.5.gif" />
</div>
</div>
<div class="section" id="id3">
<h3>1.2 Excel存储格式<a class="headerlink" href="#id3" title="Permalink to this heading">#</a></h3>
<p>Excel中的数据可以存储为不同的类型格式，其中常用的存储类似分别是xlsx、csv以及txt，一般情况下，csv文件采用是逗号分隔，txt文件采用的是制表符作为分隔，例如<code class="docutils literal notranslate"><span class="pre">data/chap1/Titanic_normal.csv</span></code>和<code class="docutils literal notranslate"><span class="pre">data/chap1/Titanic_tab.txt</span></code>，而在较为特殊的情况下，数据的分隔符合可能不是常规的分隔符合，例如<code class="docutils literal notranslate"><span class="pre">data/chap1/Titanic_special.txt</span></code>，使用记事本打开这个文件发现文件采用<code class="docutils literal notranslate"><span class="pre">|</span></code>作为分隔的符合，那么这样的文件如何使用Excel打开呢？</p>
<div class="figure align-default">
<img alt="" src="../_images/1.6.png" />
</div>
<p>这里推荐大家使用以下的操作方案，操作路径为菜单栏选择：<strong>数据–&gt;获取和转换数据–&gt;从文件/CSV</strong></p>
<div class="figure align-default">
<img alt="" src="../_images/1.7.gif" />
</div>
<p>点击<strong>加载</strong>后可以看到我们导入的数据集，接着按快捷键<code class="docutils literal notranslate"><span class="pre">ctrl+s</span></code>保存工作簿到本地。</p>
<blockquote>
<div><p>至此我们已经成功加载数据集到工作表当中，需要注意的是此时构建的数据表跟原始数据是存在连接关系，更改csv文件的数据后在点击路径：<strong>设计-外部表数据-刷新</strong>后就可以更新工作表数据。如果要取消链接关系则点击路径：<strong>设计-外部数据表-取消链接</strong>即可。</p>
</div></blockquote>
</div>
</div>
<div class="section" id="id4">
<h2>2.Excel数据格式<a class="headerlink" href="#id4" title="Permalink to this heading">#</a></h2>
<div class="section" id="id5">
<h3>2.1 单元格数据类型<a class="headerlink" href="#id5" title="Permalink to this heading">#</a></h3>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap1/2.1.xlsx</span></code>文件，点击【单元格数据类型】工作表，可以看到Excel有11种数据类型</p>
<div class="figure align-default">
<img alt="" src="../_images/2.1.png" />
</div>
<p>在平时工作和学习中常见的数据类型有3种分别是数字、文本和日期，接下来就详细讲解这3种格式的相关应用</p>
<div class="section" id="id6">
<h4>2.1.1 数字<a class="headerlink" href="#id6" title="Permalink to this heading">#</a></h4>
<p>数值是单元格最常见的数据之一，12位数的时候会以科学计数法显示。</p>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap1/2.1.xlsx</span></code>文件，点击【真假数字】工作表看到展示的是真假数值列，真数值默认情况下是右对齐（方便观测数据位数），假数值是数值型文本属于<strong>文本</strong>，左上角会有个绿色三角错误检查，当数据是文本时，无法完成数字运算操作。</p>
<div class="figure align-default">
<img alt="" src="../_images/2.2.png" />
</div>
<p>Excel会自带数据纠错功能，点击黄色感叹号可以看到，将文本转出数字，或者使用<code class="docutils literal notranslate"><span class="pre">=VALUE()</span></code>函数将文本转成数字</p>
<div class="figure align-default">
<img alt="" src="../_images/2.3.gif" />
</div>
</div>
<div class="section" id="id7">
<h4>2.1.2 文本<a class="headerlink" href="#id7" title="Permalink to this heading">#</a></h4>
<p>文本为Excel中的常见格式，主要为文本字符串，例如下图就是典型的文本数据。当数据是文本类型时就可以完成文本数据相关操作。</p>
<div class="figure align-default">
<img alt="" src="../_images/2.4.png" />
</div>
</div>
<div class="section" id="id8">
<h4>2.1.3 日期<a class="headerlink" href="#id8" title="Permalink to this heading">#</a></h4>
<p>Excel中的标准日期由年月日组成的数据，例如<code class="docutils literal notranslate"><span class="pre">2021/1/1</span></code>，同时日期又是很特殊的数值，在Excel里面日期最早是1900年1月1日，对应的数值为1，通过两日期相减可以得到日期相差的天数。需要注意的是如果是<code class="docutils literal notranslate"><span class="pre">2021-01-01</span></code>的数据就要看是否实际数据为<code class="docutils literal notranslate"><span class="pre">'2021/1/1'</span></code>，否则为文本型日期，需要进行转换才行。观察下图真假日期，可以发现真日期右对齐，假日期左对齐（内容跟显示的一致），真日期转格式可以看到单元格数据仍然是真日期格式（内容跟显示不一致）。</p>
<div class="figure align-default">
<img alt="" src="../_images/2.5.gif" />
</div>
</div>
</div>
<div class="section" id="id9">
<h3>2.2 单元格格式<a class="headerlink" href="#id9" title="Permalink to this heading">#</a></h3>
<p>设置单元格格式可以点击单元格后，<strong>右键-设置单元格格式</strong>选项，或者按快捷键<strong>ctrl+1</strong>，或者通过工具栏：<strong>开始-数字</strong>去快速做更改。单元格可以设置的格式类型包括：</p>
<ul class="simple">
<li><p>数字：调整数值格式或者日期格式。</p></li>
<li><p>对齐：调整对齐方式。</p></li>
<li><p>字体：更改字体、字型、字号等。</p></li>
<li><p>边框：单元格边框设置。</p></li>
<li><p>填充：填充单元格背景颜色，标注数据经常使用。</p></li>
<li><p>保护：锁定单元格或者隐藏公式。</p></li>
</ul>
<div class="section" id="id10">
<h4>2.2.1 数值格式<a class="headerlink" href="#id10" title="Permalink to this heading">#</a></h4>
<p>设置单元格格式分类选择数值，对数值设置小数点一般保留两位，同时能让数值对齐，使用千分位分隔符能更容易观察数值大小。</p>
</div>
<div class="section" id="id11">
<h4>2.2.2 文本格式<a class="headerlink" href="#id11" title="Permalink to this heading">#</a></h4>
<p>文本常见的设置格式用于对文本进行加单引号、双引号或者逗号的情况。</p>
<p>例如对真假日期中的假日期增加单引号</p>
<div class="figure align-default">
<img alt="" src="../_images/2.7.gif" />
</div>
</div>
<div class="section" id="id12">
<h4>2.2.3 日期格式<a class="headerlink" href="#id12" title="Permalink to this heading">#</a></h4>
<p>通过日期分类调整日期格式为年-月-日，需要注意的是文本型的日期格式无法调整，而数值是可以调整为日期。</p>
<div class="figure align-default">
<img alt="" src="../_images/2.8.png" />
</div>
</div>
</div>
<div class="section" id="id13">
<h3>2.3 转换单元格数据<a class="headerlink" href="#id13" title="Permalink to this heading">#</a></h3>
<div class="section" id="id14">
<h4>2.3.1 数值和文本相互转换<a class="headerlink" href="#id14" title="Permalink to this heading">#</a></h4>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap1/2.1.xlsx</span></code>，将数值转换成文本</p>
<div class="figure align-default">
<img alt="" src="../_images/2.9.gif" />
</div>
</div>
<div class="section" id="id15">
<h4>2.3.2 日期和文本相互转换<a class="headerlink" href="#id15" title="Permalink to this heading">#</a></h4>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap1/2.1.xlsx</span></code>，将不规范日期转成规范日期，然后转成文本</p>
<div class="figure align-default">
<img alt="" src="../_images/2.10.gif" />
</div>
</div>
</div>
</div>
<div class="section" id="id16">
<h2>练习<a class="headerlink" href="#id16" title="Permalink to this heading">#</a></h2>
<ol class="arabic simple">
<li><p>使用Excel数据导入功能，完成<code class="docutils literal notranslate"><span class="pre">data/chap1/Titanic_normal.csv</span></code>和<code class="docutils literal notranslate"><span class="pre">data/chap1/Titanic_train.txt</span></code>数据导入</p></li>
<li><p>完成<code class="docutils literal notranslate"><span class="pre">data/chap1/2.1.xlsx</span></code>中案例1-4中的问题</p></li>
</ol>
</div>
</div>


              </article>
              

              
          </div>
          
      </div>
    </div>

  
  
  <!-- Scripts loaded after <body> so the DOM is not blocked -->
  <script src="../_static/scripts/pydata-sphinx-theme.js?digest=92025949c220c2e29695"></script>

<footer class="bd-footer"><div class="bd-footer__inner container">
  
  <div class="footer-item">
    <p class="copyright">
    &copy; Copyright 2022, Datawhale, 牧小熊.<br>
</p>
  </div>
  
  <div class="footer-item">
    <p class="sphinx-version">
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 5.3.0.<br>
</p>
  </div>
  
</div>
</footer>
  </body>
</html>